
DROP DATABASE project_schedule;
CREATE DATABASE project_schedule;
DROP TABLE IF EXISTS project_information;
CREATE TABLE project_information (
--   id int AUTO_INCREMENT COMMENT '自增ID', 
	project_id varchar(20) NOT NULL COMMENT '项目ID',
	project_name varchar(128) NOT NULL COMMENT '项目名称',
    dept_id INT COMMENT '部门ID',
	project_department VARCHAR(128) NOT NULL COMMENT '责任单位',
	manager VARCHAR(20) COMMENT '项目负责人',
	current_nodeId int COMMENT	'当前节点',
	project_content VARCHAR(255) COMMENT '项目简介',
	is_cancel int DEFAULT 0 COMMENT '是否取消，默认不取消',
	is_check int DEFAULT 1 COMMENT '是否考核，默认考核',
	is_lock int DEFAULT 1 COMMENT '是否锁定,1锁定，默认锁定，需解锁后才能推进进度',
	is_finished int DEFAULT 0 COMMENT '是否完成，1完成，0未完成，默认未完成',
	add_time DATE COMMENT '添加时间',
	PRIMARY KEY(project_id)
)COMMENT '项目信息表';
ALTER TABLE project_information ADD yellow_card int DEFAULT 0;
ALTER TABLE project_information ADD red_card int DEFAULT 0;


DROP TABLE IF EXISTS project_schedule;
CREATE TABLE project_schedule(
--   id int AUTO_INCREMENT COMMENT '自增ID', 
	project_id varchar(20) NOT NULL COMMENT '项目ID',
	node_id INT COMMENT '节点ID',
	node_state INT COMMENT '节点状态',
	schedule_start_time DATE COMMENT '计划开始时间',
	schedule_end_time DATE COMMENT '计划结束时间',
	delay_totime DATE COMMENT '延期到时间',
	actual_start_time DATE COMMENT	'实际开始时间',
	actual_end_time DATE COMMENT '实际结束时间',
	delay_reason varchar(255) COMMENT '滞后原因',
	PRIMARY KEY(project_id,node_id)
)COMMENT '项目进度表';

BEGIN;
INSERT INTO project_schedule VALUES(1,1,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,2,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,3,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,4,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,5,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,6,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
INSERT INTO project_schedule VALUES(1,7,0,'2024-03-01','2024-03-05','2024-06-05','2024-03-01','2024-03-06','延期原因');
COMMIT;


CREATE TABLE project_node(
	node_id INT,
	node_name VARCHAR(100),
	PRIMARY KEY(node_id)
)COMMENT '节点名称表';

BEGIN;
INSERT INTO project_node VALUES(1,'项目启动技术方案编制');
INSERT INTO project_node VALUES(2,'公司完成技术方案审批');
INSERT INTO project_node VALUES(3,'承办单位完成发包文件编制');
INSERT INTO project_node VALUES(4,'公司完成发包文件审核');
INSERT INTO project_node VALUES(5,'完成项目发包及合同签订');
INSERT INTO project_node VALUES(6,'项目开完工');
INSERT INTO project_node VALUES(7,'完成竣工验收及竣工结算');
COMMIT;

DROP TABLE IF EXISTS node_delay;
CREATE TABLE node_delay(
-- 	id int AUTO_INCREMENT COMMENT '自增ID', 
	project_id varchar(64) NOT NULL COMMENT '项目ID',
	node_id INT ,
	dept_name VARCHAR(20),
	dept_id INT,
	apply_man varchar(20), 
	apply_man_id INT,
	apply_time DATE COMMENT '申请时间',
	delay_totime DATE COMMENT '延期到时间',
	apply_reason VARCHAR(255) COMMENT '申请原因',
	approve_man VARCHAR(20) COMMENT '审批人',
	approve_time DATE COMMENT '审批时间',
	approve_comment VARCHAR(255) COMMENT '审批意见',
	approve_status int COMMENT '审批状态',
	
	PRIMARY KEY(project_id,node_id)
)COMMENT '节点延期表';


DROP TABLE IS EXISTS project_num;
CREATE TABLE project_num(
	current_month VARCHAR(10) not NULL,
	num int ,
	PRIMARY KEY(current_month)
)

DROP TABLE IF EXISTS department;
CREATE TABLE department(
	dept_id int AUTO_INCREMENT COMMENT '自增ID',
	dept_type VARCHAR(10),
	dept_name VARCHAR(20),
	origin_id VARCHAR(50),
	is_abandon int DEFAULT 0,
	abandon_time DATE,
	PRIMARY KEY(dept_id)
) COMMENT '部门配置表';
ALTER TABLE department ADD yellow_weight FLOAT DEFAULT 0.0;
ALTER TABLE department ADD red_weight FLOAT DEFAULT 0.0;

BEGIN;
INSERT INTO department(dept_type,dept_name) VALUES('各单位','隔河岩电厂');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','高坝洲电厂');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','水布垭电厂');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','检修公司');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','库坝中心');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','梯调中心');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','水电公司');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','清能公司');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','峡口塘电厂');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','物业公司');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','长阳抽蓄公司');
INSERT INTO department(dept_type,dept_name) VALUES('各单位','峡口塘建设项目部');

INSERT INTO department(dept_type,dept_name) VALUES('本部部门','生产技术与营销部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','信息管理部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','经营管理部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','办公室');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','人力资源部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','资产财务部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','投资发展部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','质量安全部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','党群工作部');
INSERT INTO department(dept_type,dept_name) VALUES('本部部门','纪检工作部');
COMMIT;



DROP TABLE IF EXISTS user;
CREATE TABLE user(
	uid int,
	username VARCHAR(20),
	password VARCHAR(50),
	PRIMARY KEY(uid)
);
BEGIN ;
INSERT INTO user VALUES(1,'员工1','123');
INSERT INTO user VALUES(2,'员工2','123');
INSERT INTO user VALUES(3,'员工3','123');
INSERT INTO user VALUES(4,'员工4','123');
COMMIT;


DROP TABLE IF EXISTS user_info;
CREATE table user_info(
	uid int,
	username varchar(50),
	roleId int,
	deptId int,
	is_abandon int DEFAULT 0,
	abandon_time DATE,
	istaffid BIGINT,
	sstaffname VARCHAR(50),
	sstaffaccount VARCHAR(50),
	PRIMARY KEY(uid)
);
BEGIN ;
INSERT INTO user_info(uid,username,roleId,deptId) VALUES(1,'员工1',1,1);
INSERT INTO user_info(uid,username,roleId,deptId) VALUES(2,'员工2',2,1);
INSERT INTO user_info(uid,username,roleId,deptId) VALUES(3,'员工3',3,1);
INSERT INTO user_info(uid,username,roleId,deptId) VALUES(4,'员工4',4,1);
COMMIT;





CREATE TABLE role(
	roleId int,
	role_name VARCHAR(20),
	PRIMARY KEY(roleId)
);
INSERT INTO role(roleId,role_name) VALUES(1,'系统管理员');
INSERT INTO role(roleId,role_name) VALUES(2,'生技部管理员');
INSERT INTO role(roleId,role_name) VALUES(3,'领导');
INSERT INTO role(roleId,role_name) VALUES(4,'部门管理员');


CREATE TABLE manage_dept(
	uid int,
	dept_id int,
	dept_name VARCHAR(20),
	PRIMARY KEY(uid,dept_id)
)


CREATE TABLE config(
	id int AUTO_INCREMENT COMMENT '自增ID',
	description VARCHAR(20),
	days int,
	info VARCHAR(255),
	PRIMARY KEY(id)
);

INSERT INTO config(description,days,info) VALUES('yellow_card',30,'指定天数内为黄牌，超过则为红牌');
INSERT INTO config(description,days,info) VALUES('message',5,'发送短信提醒');



-- DROP TABLE IF EXISTS node_dailyrecord;
-- CREATE TABLE node_dailyrecord(
-- --   id int AUTO_INCREMENT COMMENT '自增ID', 
-- 	project_id varchar(64) NOT NULL COMMENT '项目ID',
-- 	current_nodeId INT ,
-- 	node_state INT,
-- 	node_finish_time DATE,
-- 	record_time DATE ,
-- -- 	cur_time TIME COMMENT '当前时间',
-- 	PRIMARY KEY(project_id,current_nodeId)
-- -- 	PRIMARY KEY(cur_time)
-- ) COMMENT '每日节点状态记录表';
-- 
-- 
-- DROP EVENT IF EXISTS daily_record;
-- CREATE EVENT IF NOT EXISTS daily_record
-- ON SCHEDULE EVERY 1 DAY
-- STARTS NOW()
-- ON COMPLETION PRESERVE ENABLE
-- COMMENT '定期获取时间更新数据库'
-- DO 
-- begin
-- INSERT INTO node_dailyrecord(project_id,current_nodeId,node_finish_time,record_time)
-- SELECT info.project_id,info.current_nodeId,sch.delay_totime AS node_finish_time,CURDATE() AS record_time
-- FROM project_information AS info
-- LEFT JOIN project_schedule AS sch
-- ON info.project_id=sch.project_id AND info.current_nodeId=sch.node_id
-- WHERE info.is_finished=0;
-- end;
-- 
-- 
-- SHOW EVENTS;
-- 
-- # 启动事件
-- alter event event_t1 enable;
-- # 关闭事件
-- alter event event_t1 disable;
-- 
-- show VARIABLES like '%event_schedule%';
-- SET GLOBAL event_scheduler = ON;